﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SQLite;

namespace WebBrowserTest.DAL
{
   
    public class SeoDAL
    {
        #region Constants
        struct CreateTablesScripts
        {
            public static readonly string Project = "CREATE TABLE [Project](" +
                                                    "[ProjectId] INTEGER PRIMARY KEY NOT NULL," +
                                                    "[Keyword] [text] NOT NULL" +
                                                    ")";

            public static readonly string Domain = "CREATE TABLE [Domain]("+
                                                    "[DomainId] INTEGER PRIMARY KEY NOT NULL," +
                                                    "[ProjectId] [int] NOT NULL,"+
                                                    "[GooglePosition] [int]," +
                                                    "[DomainAge] [nchar](128) NULL," +
                                                    "[PageRank] [int] NULL,"+
                                                    "[Site] [int] NULL,"+
                                                    "[Bld] [int] NULL,"+
                                                    "[Dmoz] [int] NULL,"+
                                                    "[CacheAge] [int] NULL,"+
                                                    "[Title] [nchar](10) NULL,"+
                                                    "[Url] [nchar](10) NULL,"+
                                                    "[Description] [nchar](10) NULL,"+
                                                    "[HTags] [nchar](10) NULL,"+
                                                    "[Density] [float] NULL," +
                                                    "[UniqueContent] [nchar](10) NULL,"+
                                                    "[CreateDate] [datetime] NULL,"+
                                                    "[IsOuterDomain] [tinyint] NOT NULL,"+
                                                    "[DomainName] [nchar](255) NULL," +
                                                    "[AnalyzeHistoryId] [int] NOT NULL,"+
                                                    "[IsHistorical][int] DEFAULT 0 NOT NULL," +
                                                    "[DomainEntryId] [int] NOT NULL" +
                                                    ") ";
            public static readonly string AnalyzeDomain = "CREATE TABLE [AnalyzeDomain](" +
                                                    "[AnalyzeDomainId] INTEGER PRIMARY KEY NOT NULL," +
                                                    "[DomainId] [int] NOT NULL," +
                                                    "[GooglePosition] [int]," +
                                                    "[AnalyzeDetails] [int] NOT NULL," +
                                                    "[DomainAge] [int] NULL," +
                                                    "[PageRank] [int] NULL," +
                                                    "[Site] [int] NULL," +
                                                    "[Bld] [int] NULL," +
                                                    "[Dmoz] [int] NULL," +
                                                    "[CacheAge] [int] NULL," +
                                                    "[Title] [nchar](10) NULL," +
                                                    "[Url] [nchar](10) NULL," +
                                                    "[Description] [nchar](10) NULL," +
                                                    "[HTags] [nchar](10) NULL," +
                                                    "[CreateDate] [datetime] NULL," +
                                                    "[DomainName] [nchar](255) NULL," +
                                                    "[AnalyzeHistoryId] [int] NOT NULL," +
                                                    "[Anchor] [nchar](255),"+
                                                    "[IP] [nchar](25)"+
                                                    ")";

            public static readonly string DetailsAnalyzeResult = "CREATE TABLE [DetailsAnalyzeResult](" +
                                                                 "[AnalyzeResultId] INTEGER PRIMARY KEY NOT NULL," +
                                                                 "[PageRankSum] [int] NULL," +
                                                                 "[UniqueDomains] [int] NULL," +
                                                                 "[UniqueMainDomains] [int] NULL," +
                                                                 "[UniqueIp] [int] NULL," +
                                                                 "[AnchorDiff] [int] NULL," +
                                                                 "[LinkFollow] [int] NULL," +
                                                                 "[LinkNotFollow] [int] NULL," +
                                                                 "[Density] [float] NULL" +
                                                                 ")";

            public static readonly string MainAnalyzeHistory = "CREATE TABLE [MainAnalyzeHistory](" +
                                                                 "[MainAnalyzeHistoryId] INTEGER PRIMARY KEY NOT NULL," +
                                                                 "[CreateDate] [datetime] NOT NULL" +
                                                                 ")";
            public static readonly string AnalyzeDetailsHistory = "CREATE TABLE [AnalyzeDetailsHistory](" +
                                                                 "[AnalyzeDetailsHistoryId] INTEGER PRIMARY KEY NOT NULL," +
                                                                 "[CreateDate] [datetime] NOT NULL" +
                                                                 ")"; 
        }
        struct TableNames
        {
            public const string Project = "Project";
            public const string Domain = "Domain";
            public const string AnalyzeDomain = "AnalyzeDomain";
            public const string DetailsAnalyzeResult = "DetailsAnalyzeResult";
            public const string MainAnalyzeHistory = "MainAnalyzeHistory";
            public const string AnalyzeDetailsHistory = "AnalyzeDetailsHistory";
        }
        #endregion

        #region Fields
        private string _connectionString = ""; 
        #endregion

        #region .ctor
        public SeoDAL()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["seoDB"].ConnectionString;
        } 
        #endregion

        #region Create database/tables
        private string CreateDataBase()
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                    return null;
                }
                catch (Exception ex)
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    return ex.ToString();
                }

            }
        }

        private string CreateDatabaseTables()
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    //create table Project
                    if (!CheckIfTableExists("Project", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.Project;
                        command.ExecuteNonQuery();
                    }
                    if (!CheckIfTableExists("Domain", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.Domain;
                        command.ExecuteNonQuery();
                    }
                    if (!CheckIfTableExists("AnalyzeDomain", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.AnalyzeDomain;
                        command.ExecuteNonQuery();
                    }
                    if (!CheckIfTableExists("DetailsAnalyzeResult", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.DetailsAnalyzeResult;
                        command.ExecuteNonQuery();
                    }
                    if (!CheckIfTableExists("MainAnalyzeHistory", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.MainAnalyzeHistory;
                        command.ExecuteNonQuery();
                    }
                    if (!CheckIfTableExists("AnalyzeDetailsHistory", conn))
                    {
                        SQLiteCommand command = conn.CreateCommand();
                        command.CommandText = CreateTablesScripts.AnalyzeDetailsHistory;
                        command.ExecuteNonQuery();
                    }
                    conn.Close();
                    return null;
                }
                catch (Exception ex)
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    return ex.ToString();
                }
            }
        }

        public string PrepareDatabaseStructure()
        {
            string cDbREsult = CreateDataBase();
            if (cDbREsult != null)
            {
                return cDbREsult;
            }

            string cTablesResult = CreateDatabaseTables();
            if (cTablesResult != null)
            {
                return cTablesResult;
            }
            return null;
        }

        private bool CheckIfTableExists(string tableName, SQLiteConnection conn)
        {
            string query = "SELECt COUNT(*) FROM " + tableName;
            try
            {

                SQLiteCommand command = conn.CreateCommand();
                command.CommandText = query;
                command.ExecuteScalar();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        } 
        #endregion



        #region Get data
        public List<MainAnalyzerResults> GetDomains(int projectId, bool userDomains)
        {
            string sqlCommand = string.Format
                (
                "SELECT * FROM {0} WHERE ProjectId = {1} AND IsOuterDomain = {2} AND IsHistorical = 0", TableNames.Domain, projectId, userDomains ? 0 : 1

                );
            SQLiteDataReader reader = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlCommand);
                    reader = command.ExecuteReader();
                    return GetDomainResults(reader);
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }
        public MainAnalyzerResults GetHistoricalValueForDomain(int domainId, int histEntryId)
        {
            string sqlCommand = string.Format
                          (
                          "SELECT * FROM {0} WHERE DomainEntryId = {1} AND AnalyzeHistoryId = {2}", TableNames.Domain, domainId, histEntryId

                          );
            SQLiteDataReader reader = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlCommand);
                    reader = command.ExecuteReader();
                    return GetDomainResults(reader).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }

        public List<MainAnalyzerResults> GetHistoricalDomains(int domainId)
        {
            string sqlCommand = string.Format
                    (
                    "SELECT distinct d.* FROM {0} as d join {3} as ad on d.DomainEntryId = ad.DomainId WHERE DomainEntryId = {1} AND IsOuterDomain = {2}", TableNames.Domain, domainId, 0,
                    TableNames.AnalyzeDomain

                    );
            SQLiteDataReader reader = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlCommand);
                    reader = command.ExecuteReader();
                    var domains =  GetDomainResults(reader);
                    return domains;
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }

        public List<MainAnalyzerResults> GetHistoricalDomainsForProject(int projectId)
        {
            string sqlCommand = string.Format
                    (
                    "SELECT * FROM {0} WHERE ProjectId = {1} AND IsOuterDomain = {2}", TableNames.Domain, projectId, 1

                    );
            SQLiteDataReader reader = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlCommand);
                    reader = command.ExecuteReader();
                    return GetDomainResults(reader);
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }

        public List<HistoryEntry> GetHistoryEntriesForDomain(int domainId)
        {
            List<HistoryEntry> result = new List<HistoryEntry>();
            var domain = GetHistoricalDomains(domainId);
            
            List<DateTime> histIds = domain.Select(o => o.CheckDate.Date).ToList();
            foreach(var date in histIds)
            {
                if(!result.Select(o=>o.Date).Contains(date))
                {
                    result.Add(new HistoryEntry{Date = date, Id = 0});
                }
            }
            return result;

           // string histSql = string.Format("SELECT * FROM {0}", TableNames.MainAnalyzeHistory);


            #region  not used
            //using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            //{
            //    SQLiteDataReader reader = null;
            //    try
            //    {
            //        conn.Open();
            //        var command = CreateCommand(conn, histSql);
            //        reader = command.ExecuteReader();
            //        while (reader.Read())
            //        {
            //            DateTime dt = reader.GetDateTime(1);
            //            int id = reader.GetInt32(0);
            //            if(histIds.Contains(id))
            //            {
            //                result.Add(new HistoryEntry{Date = dt,Id = id});
            //            }
            //        }

            //        return result;

            //    }
            //    catch (Exception ex)
            //    {
            //        return null;
            //    }
            //    finally
            //    {
            //        if (reader != null)
            //        {
            //            reader.Close();
            //        }
            //        if (conn != null)
            //        {
            //            conn.Close();
            //        }
            //    }
            //} 
            #endregion

        }

        public HistoryDomainCompareTO GetHistoryDataToCompareForDomain(int domainId, DateTime date)
        {
            string sqlSelectDomainCommand = string.Format
             (
             "SELECT * FROM {0} WHERE DomainId = {1} ", TableNames.AnalyzeDomain, domainId

             );
            SQLiteDataReader reader = null;
            int detailsId = -1;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlSelectDomainCommand);
                    reader = command.ExecuteReader();
                    var result = GetDetailsAnalyzeDomainResults(reader);

                    var details = result.Where(o => o.CheckDate.Date == date.Date).FirstOrDefault();
                    if (details != null)
                    {
                        detailsId = details.DetailsId;
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }

            var mainAnalyzeResult = GetHistoricalDomains(domainId).Where(o => o.CheckDate.Date == date.Date).OrderBy(o=>o.CheckDate).FirstOrDefault();
            var detailsAnalyzeResult =
                GetDetailsAnalyzeDomains(domainId, detailsId);



            return new HistoryDomainCompareTO { AnalyzeDetailsTO = detailsAnalyzeResult, MainAnalyze = mainAnalyzeResult };
        }


        public AnalyzeDetailsTO GetDetailsAnalyzeDomains(int parentDomainId, int detailsId)
        {
            AnalyzeDetailsTO result = new AnalyzeDetailsTO();
            string sqlSelectDomainCommand = string.Format
               (
               "SELECT * FROM {0} WHERE DomainId = {1} ", TableNames.AnalyzeDomain, parentDomainId

               );


            SQLiteDataReader reader = null;
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, sqlSelectDomainCommand);
                    reader = command.ExecuteReader();
                    result.DetailsDomainAnalyzeResults = GetDetailsAnalyzeDomainResults(reader);
                    if (result.DetailsDomainAnalyzeResults.Count == 0)
                    {
                        return result;
                    }
                    if(detailsId < 0)
                    {
                        detailsId =
                            result.DetailsDomainAnalyzeResults.OrderByDescending(o => o.CheckDate).FirstOrDefault().
                                DetailsId;
                    }
                    string sqlSelectDetailsCommand = string.Format("SELECT * FROM {0} WHERE AnalyzeResultId = {1}",
                                                        TableNames.DetailsAnalyzeResult,
                                                        detailsId);
                    var detailsComm = CreateCommand(conn, sqlSelectDetailsCommand);
                    reader = detailsComm.ExecuteReader();
                    result.AnalyzeDetails = GetAnalyzeDetails(reader);
                    return result;

                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public List<Project> GetProjects()
        {
            string selectSql = "SELECT * FROM " + TableNames.Project;
            SQLiteDataReader reader = null;
            List<Project> resultProjects = new List<Project>();
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, selectSql);
                    reader = command.ExecuteReader();
                    resultProjects = GetProjectsResults(reader);

                    return resultProjects;

                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public List<MainAnalyzerResults> GetLastAddedDetailsAnalyze()
        {
            string lastInsertedRow = string.Format("SELECT AnalyzeResultId FROM {0} order by AnalyzeResultId desc limit 1;", TableNames.DetailsAnalyzeResult);
            string selectAnalyzeDetailsDomains = "SELECT * FROM {0} WHERE AnalyzeDetails = {1}";
            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var rowCommand = CreateCommand(conn, lastInsertedRow);
                    int lastInsertedId = int.Parse(rowCommand.ExecuteScalar().ToString());
                    var selectComm = CreateCommand(conn,
                                                   string.Format(selectAnalyzeDetailsDomains, TableNames.AnalyzeDomain,
                                                                 lastInsertedId));
                    var reader = selectComm.ExecuteReader();
                    var results = GetDetailsAnalyzeDomainResults(reader);

                    List<MainAnalyzerResults> mainAnalyze = new List<MainAnalyzerResults>();
                    foreach (var result in results)
                    {
                        mainAnalyze.Add(new MainAnalyzerResults
                               {
                                   GooglePosition = result.GooglePosition,
                                   DomainAge = result.DomainAge.ToString(),
                                   PageRank = result.PageRank,
                                   Site = result.Site,
                                   Bld = result.Bld,
                                   Dmoz = result.Dmoz,
                                   CacheAge = result.CacheAge,
                                   Title = result.Title,
                                   Url = result.Url,
                                   Desc = result.Desc,
                                   HTags = result.HTags,
                                   CheckDate = result.CheckDate,
                                   Domain = result.Domain,
                                   Id = result.Id,
                                   Anchor = result.Anchor,
                                   Ip = result.Ip
                               });
                    }
                    return mainAnalyze;
                    conn.Close();


                }
                catch (Exception ex)
                {
                    return null;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }

        #endregion

        #region Delete data
        public void DeleteProject(int projectId)
        {
            string deleteSql = "DELETE FROM {0} WHERE ProjectId = {1}";
            string deleteDetailsSql = "DELETE FROM {0} WHERE DomainId IN ({1})";
            var domainsToDelete = GetDomains(projectId, true).Select(o => o.Id).ToList();
            string domainsToDelPattern = string.Join(",", domainsToDelete);
            if (domainsToDelPattern.Trim() != string.Empty && domainsToDelete.Count > 1)
            {
                domainsToDelPattern = domainsToDelPattern.Substring(0, domainsToDelPattern.LastIndexOf(","));
            }

            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, string.Format(deleteSql, TableNames.Domain, projectId));
                    command.ExecuteNonQuery();
                    command = CreateCommand(conn, string.Format(deleteSql, TableNames.Project, projectId));
                    command.ExecuteNonQuery();
                    if (domainsToDelPattern.Trim() != string.Empty)
                    {
                        command = CreateCommand(conn,
                                                string.Format(deleteDetailsSql, TableNames.AnalyzeDomain,
                                                              domainsToDelPattern));
                        command.ExecuteNonQuery();
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        } 
        #endregion

        #region Add data
        public string AddDomain(int projectId, MainAnalyzerResults analyzerResults, bool userDomain)
        {
            string insertCommand = string.Format(
                "INSERT INTO {0}(ProjectId,GooglePosition, DomainAge, PageRank, Site, Bld, Dmoz, CacheAge, Title, Url, Description," +
                                 "HTags, Density, UniqueContent, CreateDate, IsOuterDomain, DomainName, AnalyzeHistoryId, DomainEntryId ) VALUES" +
                                 "(@ProjectId, @GooglePosition, @DomainAge, @PageRank, @Site, @Bld, @Dmoz, @CacheAge, @Title, @Url, @Description," +
                                 "@HTags, @Density, @UniqueContent, @CreateDate, @IsOuterDomain , @DomainName, @AnalyzeHistoryId, @DomainEntryId)", TableNames.Domain
                );
            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();

                    //update all other analyses for this domain to historical
                    if(analyzerResults.Id > 0)
                    {
                        string updHistorical = string.Format("UPDATE {0} SET IsHistorical = 1, DomainEntryId= {1} WHERE DomainId = {1} OR DomainEntryId = {1} AND IsOuterDomain = 0", TableNames.Domain, analyzerResults.Id);
                        var updCommand = CreateCommand(conn, updHistorical);
                        updCommand.ExecuteNonQuery();
                    }


                    int analyzeHistoryId = userDomain ? AddAnalyzeHistoryEntry(TableNames.MainAnalyzeHistory) : -1;
                    var command = CreateCommand(conn, insertCommand);
                    command.Parameters.AddWithValue("@ProjectId", projectId);
                    command.Parameters.AddWithValue("@GooglePosition", analyzerResults.GooglePosition);
                    command.Parameters.AddWithValue("@DomainAge", analyzerResults.DomainAge);
                    command.Parameters.AddWithValue("@PageRank", analyzerResults.PageRank);
                    command.Parameters.AddWithValue("@Site", analyzerResults.Site);
                    command.Parameters.AddWithValue("@Bld", analyzerResults.BackLinks);
                    command.Parameters.AddWithValue("@Dmoz", analyzerResults.Dmoz);
                    command.Parameters.AddWithValue("@CacheAge", analyzerResults.CacheAge);
                    command.Parameters.AddWithValue("@Title", analyzerResults.Title);
                    command.Parameters.AddWithValue("@Url", analyzerResults.Url);
                    command.Parameters.AddWithValue("@Description", analyzerResults.Desc);
                    command.Parameters.AddWithValue("@HTags", analyzerResults.HTags);
                    command.Parameters.AddWithValue("@Density", analyzerResults.Density);
                    command.Parameters.AddWithValue("@UniqueContent", analyzerResults.Unique);
                    command.Parameters.AddWithValue("@CreateDate", analyzerResults.CheckDate);
                    command.Parameters.AddWithValue("@DomainName", analyzerResults.Domain);
                    command.Parameters.AddWithValue("@IsOuterDomain", userDomain ? 0 : 1);
                    command.Parameters.AddWithValue("@AnalyzeHistoryId", analyzeHistoryId);
                    command.Parameters.AddWithValue("@DomainEntryId", analyzerResults.Id);
                    command.ExecuteNonQuery();
                    conn.Close();
                    return null;

                }
                catch (Exception ex)
                {
                    return ex.ToString();
                    throw;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public string AddAnalyzeDomain(List<MainAnalyzerResults> analyzerResults, AnalyzeDetails analyzeDetails, int domainId)
        {
            if (analyzerResults.Count == 0)
            {
                return null;
            }
            string insertCommand = string.Format(
            "INSERT INTO {0}(DomainId,GooglePosition, AnalyzeDetails ,DomainAge, PageRank, Site, Bld, Dmoz, CacheAge, Title, Url, Description," +
                                "HTags, CreateDate, DomainName, AnalyzeHistoryId, Anchor, IP) VALUES" +
                                "(@DomainId,@GooglePosition, @AnalyzeDetails, @DomainAge, @PageRank, @Site, @Bld, @Dmoz, @CacheAge, @Title, @Url, @Description," +
                                "@HTags, @CreateDate, @DomainName, @AnalyzeHistoryId, @Anchor, @IP)", TableNames.AnalyzeDomain
            );

            int analyzeDetailsEmptyId = AddAnalyzeDetails(analyzeDetails);
            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    int analyzeHistoryId = AddAnalyzeHistoryEntry(TableNames.MainAnalyzeHistory);
                    conn.Open();
                    foreach (MainAnalyzerResults analyzerResult in analyzerResults)
                    {
                        var command = CreateCommand(conn, insertCommand);
                        command.Parameters.AddWithValue("@DomainId", domainId);
                        command.Parameters.AddWithValue("@GooglePosition", analyzerResult.GooglePosition);
                        command.Parameters.AddWithValue("@AnalyzeDetails", analyzeDetailsEmptyId);
                        command.Parameters.AddWithValue("@DomainAge", analyzerResult.DomainAge);
                        command.Parameters.AddWithValue("@PageRank", analyzerResult.PageRank);
                        command.Parameters.AddWithValue("@Site", analyzerResult.Site);
                        command.Parameters.AddWithValue("@Bld", analyzerResult.BackLinks);
                        command.Parameters.AddWithValue("@Dmoz", analyzerResult.Dmoz);
                        command.Parameters.AddWithValue("@CacheAge", analyzerResult.CacheAge);
                        command.Parameters.AddWithValue("@Title", analyzerResult.Title);
                        command.Parameters.AddWithValue("@Url", analyzerResult.Url);
                        command.Parameters.AddWithValue("@Description", analyzerResult.Desc);
                        command.Parameters.AddWithValue("@HTags", analyzerResult.HTags);
                        command.Parameters.AddWithValue("@CreateDate", analyzerResult.CheckDate);
                        command.Parameters.AddWithValue("@DomainName", analyzerResult.Domain);
                        command.Parameters.AddWithValue("@AnalyzeHistoryId", analyzeHistoryId);
                        command.Parameters.AddWithValue("@AnalyzeHistoryId", analyzeHistoryId);
                        command.Parameters.AddWithValue("@Anchor", analyzerResult.Anchor);
                        command.Parameters.AddWithValue("@IP", analyzerResult.Ip);
                        command.ExecuteNonQuery();
                    }
                    conn.Close();
                    return null;

                }
                catch (Exception ex)
                {
                    return ex.ToString();
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public int AddProject(string keyword)
        {
            string insertProjectSql = string.Format("INSERT INTO {0}(Keyword) VALUES(@keyword)", TableNames.Project);

            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, insertProjectSql);
                    command.Parameters.AddWithValue("@keyword", keyword);
                    command.ExecuteNonQuery();
                    string lastInsertedRow = string.Format("SELECT last_insert_rowid() FROM {0}", TableNames.Project);
                    var rowCommand = CreateCommand(conn, lastInsertedRow);
                    int lastInsertedId = int.Parse(rowCommand.ExecuteScalar().ToString());
                    conn.Close();
                    return lastInsertedId;

                }
                catch (Exception ex)
                {
                    return -1;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        private int AddAnalyzeDetails(AnalyzeDetails analyzeDetails)
        {
            string detailsInsert = string.Format("INSERT INTO {0}(PageRankSum, UniqueDomains, UniqueMainDomains," +
                                                 " UniqueIp, AnchorDiff, LinkFollow, LinkNotFollow, Density )" +
                                                "VALUES(@PageRankSum, @UniqueDomains, @UniqueMainDomains," +
                                                 "@UniqueIp, @AnchorDiff, @LinkFollow, @LinkNotFollow, @Density)", TableNames.DetailsAnalyzeResult);
            string lastInsertedRow = string.Format("SELECT last_insert_rowid() FROM {0}", TableNames.DetailsAnalyzeResult);
            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, detailsInsert);
                    command.Parameters.AddWithValue("@PageRankSum", analyzeDetails.PageRankSum);
                    command.Parameters.AddWithValue("@UniqueDomains", analyzeDetails.UniqueDomains);
                    command.Parameters.AddWithValue("@UniqueMainDomains", analyzeDetails.UniqueMainDomains);
                    command.Parameters.AddWithValue("@UniqueIp", analyzeDetails.UniqueIp);
                    command.Parameters.AddWithValue("@AnchorDiff", analyzeDetails.AnchorDiff);
                    command.Parameters.AddWithValue("@LinkFollow", analyzeDetails.LinkFollow);
                    command.Parameters.AddWithValue("@LinkNotFollow", analyzeDetails.LinkNotFollow);
                    command.Parameters.AddWithValue("@Density", analyzeDetails.Density);
                    command.ExecuteNonQuery();

                    var rowCommand = CreateCommand(conn, lastInsertedRow);
                    int lastInsertedId = int.Parse(rowCommand.ExecuteScalar().ToString());
                    conn.Close();
                    return lastInsertedId;

                }
                catch (Exception ex)
                {
                    return -1;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
        public int AddAnalyzeHistoryEntry(string tableName)
        {
            string historySql = string.Format("INSERT INTO {0} (CreateDate) VALUES (@CreateDate)", tableName);
            string lastInsertedRow = string.Format("SELECT last_insert_rowid() FROM {0}", tableName);
            using (var conn = new SQLiteConnection(_connectionString))
            {
                try
                {
                    conn.Open();
                    var command = CreateCommand(conn, historySql);
                    command.Parameters.AddWithValue("@CreateDate", DateTime.Now);
                    command.ExecuteNonQuery();

                    var rowCommand = CreateCommand(conn, lastInsertedRow);
                    int lastInsertedId = int.Parse(rowCommand.ExecuteScalar().ToString());
                    conn.Close();
                    return lastInsertedId;

                }
                catch (Exception ex)
                {
                    return -1;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }

        } 
        #endregion




        #region Helper Methods
        private SQLiteCommand CreateCommand(SQLiteConnection conn, string cmdText)
        {
            SQLiteCommand command = conn.CreateCommand();
            command.CommandText = cmdText;
            return command;
        }
        private List<Project> GetProjectsResults(SQLiteDataReader reader)
        {
            List<Project> resultProjects = new List<Project>();
            if (reader == null)
            {
                return resultProjects;
            }
            while (reader.Read())
            {
                resultProjects.Add(new Project
                                       {
                                           Id = reader.GetInt32(0),
                                           Keyword = reader.GetString(1)
                                       });
            }
            return resultProjects;
        }
        private List<MainAnalyzerResults> GetDomainResults(SQLiteDataReader reader)
        {
            List<MainAnalyzerResults> result = new List<MainAnalyzerResults>();
            try
            {
                while (reader.Read())
                {
                    result.Add(new MainAnalyzerResults
                                   {
                                       Id = reader.GetInt32(20) == -1 ? reader.GetInt32(0) : reader.GetInt32(20),
                                       GooglePosition = reader.GetInt32(2),
                                       ProjectId = reader.GetInt32(1),
                                       BackLinks = reader.GetInt32(6),
                                       CacheAge = reader.GetInt32(8),
                                       CheckDate = reader.GetDateTime(15),
                                       Density = reader.GetFloat(13),
                                       Desc = reader.GetString(11),
                                       Dmoz = reader.GetInt32(7),
                                       DomainAge = reader.GetString(3),
                                       HTags = reader.GetString(12),
                                       Domain = reader.GetString(17),
                                       PageRank = reader.GetInt32(4),
                                       Site = reader.GetInt32(5),
                                       Title = reader.GetString(9),
                                       Unique = reader.GetString(14),
                                       Url = reader.GetValue(10) == null ? string.Empty : reader.GetValue(9).ToString(),
                                       HistoryId = reader.GetInt32(18),
                                       DomainEntryId = reader.GetInt32(0)

                                     
                                   });
                }
                return result;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                reader.Close();
            }
        }
        private List<DetailsDomainAnalyzeResults> GetDetailsAnalyzeDomainResults(SQLiteDataReader reader)
        {
            List<DetailsDomainAnalyzeResults> result = new List<DetailsDomainAnalyzeResults>();
            try
            {
                while (reader.Read())
                {
                    result.Add(new DetailsDomainAnalyzeResults
                    {
                        Id = reader.GetInt32(0),
                        ParentDomainId = reader.GetInt32(1),
                        GooglePosition = reader.GetInt32(2),
                        DetailsId = reader.GetInt32(3),
                        BackLinks = reader.GetInt32(7),
                        CacheAge = reader.GetInt32(9),
                        CheckDate = reader.GetDateTime(14),
                        Desc = reader.GetString(12),
                        Dmoz = reader.GetInt32(8),
                        DomainAge = int.Parse(reader.GetValue(4).ToString()),
                        HTags = reader.GetString(13),
                        Domain = reader.GetString(15),
                        PageRank = reader.GetInt32(5),
                        Site = reader.GetInt32(6),
                        Title = reader.GetString(10),
                        Url = reader.GetValue(11) == null ? string.Empty : reader.GetValue(9).ToString(),
                        Anchor = reader.GetString(17),
                        Ip = reader.GetString(18)
                    });
                }
                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                reader.Close();
            }
        } 

        private List<AnalyzeDetails> GetAnalyzeDetails(SQLiteDataReader reader)
        {
            List<AnalyzeDetails> result = new List<AnalyzeDetails>();
            try
            {
                while (reader.Read())
                {
                    result.Add( new AnalyzeDetails
                    {
                        Id = reader.GetInt32(0),
                        AnchorDiff = int.Parse(reader.GetValue(5).ToString()),
                        LinkNotFollow = int.Parse(reader.GetValue(7).ToString()),
                        Density = reader.GetFloat(8),
                        LinkFollow = int.Parse(reader.GetValue(6).ToString()),
                        PageRankSum = int.Parse(reader.GetValue(1).ToString()),
                        UniqueDomains = int.Parse(reader.GetValue(2).ToString()),
                        UniqueIp = int.Parse(reader.GetValue(4).ToString()),
                        UniqueMainDomains = int.Parse(reader.GetValue(3).ToString())
                    });
                    
                }
                return result;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                reader.Close();
            }
        }

        
        #endregion
    }
}

